package com.inspect.dbConn;

import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.inspect.log.LogSingleton;
import com.inspect.protocol.Inspect_sz;

public class dbSingleton {
	private static Logger log = LogSingleton.get_logger(dbSingleton.class);
	private static dbSingleton instance = null;
	private Connection db_connect = null;
	public static boolean init_mark = false;
	private static Properties prop = new Properties();

	private dbSingleton() {

	}

	public static synchronized dbSingleton get_instance() {
		if (instance == null) {
			instance = new dbSingleton();
			instance.init();
			return instance;
		} else {
			return instance;
		}
	}

	public synchronized void init() {
		if (init_mark == true) {
			return;
		}
		try {
			prop.load(new InputStreamReader(
					Inspect_sz.class.getClassLoader().getResourceAsStream("sysConfig.properties"), "UTF-8"));
			
			String oracleDriver = prop.getProperty("inspect.driver");
			String oracleUrl = prop.getProperty("inspect.url");
			String oracleUser = prop.getProperty("inspect.user");
			String oraclePassword = prop.getProperty("inspect.password");

			if (!oracleUrl.equals("") && oracleUrl != null && !oracleUser.equals("") && oracleUser != null
					&& !oraclePassword.equals("") && oraclePassword != null) {
				Class.forName(oracleDriver).newInstance();
				this.db_connect = DriverManager.getConnection(oracleUrl, oracleUser, oraclePassword);
				init_mark = true;
			} else {
				return;
			}
		} catch (Exception e) {
			log.error("初始化数据库连接异常", e);
		}

	}

	/* return a global connect */
	public Connection get_db_conn() {
		return this.db_connect;
	}

	public boolean db_execute(String sql) {
		Statement stmt = null;
		boolean b = false;

		try {
			stmt = this.db_connect.createStatement();
			b = stmt.execute(sql);
		} catch (Exception e) {
			log.error("执行sql异常", e);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				log.error("关闭资源时异常", e);
			}
		}

		return b;

	}

	/* update action, such as update insert delete */
	public int db_update(String sql) {
		Statement stmt = null;
		int b = 0;

		try {
			stmt = this.db_connect.createStatement();
			b = stmt.executeUpdate(sql);
		} catch (Exception e) {
			log.error("执行sql更新异常", e);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				log.error("关闭资源时异常", e);
			}
		}

		return b;
	}

	public ArrayList<HashMap<String, String>> db_query_assoc(String sql) {
		ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();

		Statement stmt = null;
		ResultSet rs = null;

		ResultSetMetaData rsmd = null;

		if (this.db_connect == null) {
			return null;
		}

		try {
			stmt = this.db_connect.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int columns_num = rsmd.getColumnCount();
			while (rs.next()) {
				HashMap<String, String> hm = new HashMap<String, String>();
				for (int i = 0; i < columns_num; i++) {
					// System.out.println(rsmd.getColumnName(i+1)+ ' ' + rs.getString(i+1));
					hm.put(rsmd.getColumnName(i + 1), rs.getString(i + 1));
				}

				al.add(hm);
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			log.error("执行sql查询异常", e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				log.error("关闭资源时异常", e);
			}
		}
		return al;
	}

	public ArrayList<ArrayList<String>> db_query_array(String sql) {
		ArrayList<ArrayList<String>> al = new ArrayList<ArrayList<String>>();

		Statement stmt = null;
		ResultSet rs = null;

		ResultSetMetaData rsmd = null;

		if (this.db_connect == null) {
			return null;
		}

		try {
			stmt = this.db_connect.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int columns_num = rsmd.getColumnCount();

			while (rs.next()) {
				ArrayList<String> al_temp = new ArrayList<String>();
				for (int i = 0; i < columns_num; i++) {
					al_temp.add(rs.getString(i));
				}

				al.add(al_temp);
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			log.error("执行sql查询异常", e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				log.error("关闭资源时异常", e);
			}
		}
		return al;
	}

}